package net.siufung.boot.datasource.utils;

import cn.hutool.core.convert.Convert;
import cn.hutool.core.util.ArrayUtil;
import com.google.common.base.Strings;
import com.google.common.collect.Lists;
import net.siufung.boot.datasource.dto.ConnDto;
import net.siufung.boot.datasource.dto.TableNamesDto;
import net.siufung.boot.datasource.entity.TableField;
import net.siufung.boot.datasource.entity.TableInfo;
import net.siufung.boot.datasource.enums.DbDrivers;
import net.siufung.boot.datasource.enums.DbTypes;
import net.siufung.boot.datasource.enums.FieldEmptyValues;
import net.siufung.boot.datasource.rules.IDbKeyWordsHandler;
import net.siufung.boot.datasource.rules.IDbQuery;
import net.siufung.boot.datasource.rules.IDbType;
import net.siufung.core.exception.BizServiceException;
import net.siufung.core.model.EnumsVo;
import net.siufung.core.utils.StringPool;
import org.springframework.util.CollectionUtils;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.Objects;
import java.util.Optional;
import java.util.stream.Collectors;

/**
 * @author 陈建峰
 * @since 2022/8/15 6:07 下午
 */
public class DatasourceUtil {

    /**
     * 获得数据源链接
     * @return Connection
     */
    public static Connection getConn(ConnDto connDto) {
        Connection conn;
        try {
            Class.forName(DbDrivers.get(connDto.getDbType()).getDesc());
            conn = DriverManager.getConnection(connDto.getUrl(), connDto.getUsername(), connDto.getPassword());
            if(Optional.ofNullable(connDto.getSchemaName()).isPresent()){
                conn.setSchema(connDto.getSchemaName());
            }
        } catch (ClassNotFoundException | SQLException e) {
            throw new BizServiceException(e.getMessage());
        }
        return conn;
    }

    /**
     * 获得数据库表集合
     * @param tableNamesDto 表名称集合
     * @return List<TableInfo>
     */
    public static List<TableInfo> getTableInfoList(TableNamesDto tableNamesDto){
        return getTableInfoList(tableNamesDto, false);
    }

    /**
     * 获得数据库表集合
     * @param tableNamesDto 表名称集合
     * @param ignoreField 忽略加载TableField
     * @return List<TableInfo>
     */
    public static List<TableInfo> getTableInfoList(TableNamesDto tableNamesDto, Boolean ignoreField) {
        List<TableInfo> tableInfoList = Lists.newArrayList();
        IDbType dbType = tableNamesDto.getDbType();
        if(Optional.ofNullable(dbType).isPresent()){
            try {
                Connection connection = tableNamesDto.getConnection();
                IDbQuery dbQuery = dbType.getDbQueryByType();
                String tablesSql = dbQuery.getTablesSql();
                if(DbTypes.POSTGRE_SQL.equals(DbTypes.get(dbType.getValue()))
                        || DbTypes.ORACLE.equals(DbTypes.get(dbType.getValue()))){
                    tablesSql = String.format(tablesSql, connection.getSchema());
                }
                StringBuilder sqlBuilder = new StringBuilder(tablesSql);
                String[] tableNames = tableNamesDto.getTableNames();
                if(ArrayUtil.isNotEmpty(tableNames)){
                    sqlBuilder.append(" AND ").append(dbQuery.getTableName()).append(" IN (")
                            .append(Arrays.stream(tableNames)
                                    .map(table -> "'" + table + "'")
                                    .collect(Collectors.joining(","))).append(")");
                }
                TableInfo tableInfo;
                PreparedStatement preparedStatement = connection.prepareStatement(sqlBuilder.toString());
                ResultSet results = preparedStatement.executeQuery();
                while (results.next()) {
                    final String tableName = results.getString(dbQuery.getTableName());
                    tableInfo = new TableInfo();
                    tableInfo.setName(tableName);
                    String commentColumn = dbQuery.getTableComment();
                    if (!Strings.isNullOrEmpty(commentColumn)) {
                        String tableComment = results.getString(commentColumn);
                        tableInfo.setComment(formatComment(tableComment));
                    }
                    tableInfoList.add(tableInfo);
                }
                if(!ignoreField){
                    if(!CollectionUtils.isEmpty(tableInfoList)){
                        for (TableInfo table: tableInfoList) {
                            convertTableFields(connection, dbType, table);
                        }
                    }
                }
            } catch (SQLException e) {
                throw new BizServiceException(e.getMessage());
            }
        }
        return tableInfoList;
    }

    static void convertTableFields(Connection connection, IDbType dbType, TableInfo tableInfo) {
        TableField tableField;
        List<TableField> tableFieldList = new ArrayList<>();
        try {
            String tableName = tableInfo.getName();
            IDbQuery dbQuery = dbType.getDbQueryByType();
            String tableFieldsSql = dbQuery.getTableFieldsSql();
            if(DbTypes.POSTGRE_SQL.equals(DbTypes.get(dbType.getValue()))){
                tableFieldsSql = String.format(tableFieldsSql, tableName);
            } else if(DbTypes.ORACLE.equals(DbTypes.get(dbType.getValue()))){
                tableName = tableName.toUpperCase();
                tableFieldsSql = String.format(
                        tableFieldsSql.replace("#schema", connection.getSchema()), tableName);
            } else {
                tableFieldsSql = String.format(tableFieldsSql, tableName);
            }
            IDbKeyWordsHandler dbKeyWordsHandler = dbType.getDbKeyWordsHandlerByType();
            PreparedStatement preparedStatement = connection.prepareStatement(tableFieldsSql);
            ResultSet results = preparedStatement.executeQuery();
            int existPrimaryKeyCount = 0;
            while (results.next()) {
                tableField = new TableField();
                String fieldName = results.getString(dbQuery.getFieldName());
                boolean isId;
                String key = results.getString(dbQuery.getFieldKey());
                isId = !Strings.isNullOrEmpty(key) && "PRI".equalsIgnoreCase(key);
                if (isId) {
                    tableField.setPrimaryKey(true);
                    tableInfo.setExistPrimaryKey(true);
                    existPrimaryKeyCount ++;
                    tableField.setKeyIdentity(dbQuery.isKeyIdentity(results));
                }
                if(existPrimaryKeyCount > 1){
                    tableInfo.setExistMultiPrimaryKey(true);
                }
                if(!Objects.isNull(dbKeyWordsHandler) && dbKeyWordsHandler.isKeyWords(fieldName)){
                    System.err.printf("当前表[%s]存在字段[%s]为数据库关键字或保留字!%n", tableName, fieldName);
                    tableField.setKeyWords(true);
                    fieldName = dbKeyWordsHandler.formatColumn(fieldName);
                }
                String fieldComment = dbQuery.getFieldComment();
                if (!Strings.isNullOrEmpty(fieldComment)) {
                    tableField.setComment(formatComment(results.getString(fieldComment)));
                } else {
                    tableField.setComment(fieldName);
                }
                tableField.setName(fieldName);
                tableField.setType(results.getString(dbQuery.getFieldType()));
                tableField.setSize(tableField.getSize(tableField.getType()));
                String emptyValueKey = results.getString(dbQuery.getFieldEmptyKey());
                if(!Strings.isNullOrEmpty(emptyValueKey)){
                    for (EnumsVo enumsVo: FieldEmptyValues.getList()) {
                        if(enumsVo.getDesc().equals(emptyValueKey)){
                            tableField.setEmptyValue(Convert.toBool(enumsVo.getValue()));
                        }
                    }
                } else {
                    tableField.setEmptyValue(Boolean.TRUE);
                }
                tableFieldList.add(tableField);
            }
            tableInfo.setFieldList(tableFieldList);
        } catch (SQLException e) {
            throw new BizServiceException(e.getMessage());
        }
        if(!CollectionUtils.isEmpty(tableFieldList)){
            tableInfo.setFieldNameList(
                    tableFieldList.stream().map(TableField::getName).collect(Collectors.toList()));

        }
    }

    /**
     * 格式化数据库注释内容
     * @param comment 注释
     * @return 注释
     */
    static String formatComment(String comment) {
        return Strings.isNullOrEmpty(comment) ? StringPool.EMPTY : comment.replaceAll("\r\n", "\t");
    }


}
